﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Configuration;
using System.Data;
using System.Windows.Forms;
using DTD2013.Common;
using DTD2013.Entities;
using System.ComponentModel;

namespace DTD2013.DTDLib
{
    public class SqliteUtilities
    {
        #region Variable

        /// <summary>
        /// Sqlite connection
        /// </summary>
        private readonly SQLiteConnection Connection = null;

        #endregion

        #region Constructor

        /// <summary>
        /// Constructor
        /// </summary>
        public SqliteUtilities() 
        {
            string connectionString = ConfigurationManager.ConnectionStrings["DeluxeSqlite"].ConnectionString;
            Connection = new SQLiteConnection(connectionString);
        }

        #endregion

        #region Sqlite data base

        /// <summary>
        /// Excute query
        /// </summary>
        /// <param name="query">The sqlite query</param>
        /// <returns>1 if excute successful, otherwise return 0</returns>
        public int ExecuteNonQuery(string query)
        {
            int result = 0;
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                SQLiteCommand cmd = new SQLiteCommand(query, Connection);
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                Connection.Close();  
            }
            return result;
        }

        /// <summary>
        /// Get data table from query
        /// </summary>
        /// <param name="query">The sql query</param>
        /// <returns>Data result of query</returns>
        public DataTable GetDataTableByQuery(string query)
        {
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, Connection);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                Connection.Close();
                return dt;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            return null;
        }

        /// <summary>
        /// Generate data from standard data
        /// </summary>
        /// <param name="sourceTable">Source table name</param>
        /// <param name="destinationTable">Destination table name</param>
        public void GenerateDataFromStandardData(string sourceTable, string destinationTable)
        {
            string countString = string.Format(QueryString.GET_ROW_NUMBER_TABLE, destinationTable);
            DataTable dt = GetDataTableByQuery(countString);
            if (Convert.ToInt32(dt.Rows[0][0]) == 0)
            {
                string query = string.Format(QueryString.COPY_DATA, destinationTable, sourceTable);
                ExecuteNonQuery(query);
            }
        }

        #endregion

        #region Public method

        /// <summary>
        /// Get waste by work code in table of content
        /// </summary>
        /// <param name="workCode">The work code</param>
        /// <returns>Waste table</returns>
        public DataTable GetWasteByWorkCode(string workCode, string province)
        {
            string query = string.Format(QueryString.GET_WASTE_BY_WORKCODE, province, workCode);
            return GetDataTableByQuery(query);
        }

        /// <summary>
        /// Get mortar list
        /// </summary>
        /// <returns>Mortar list</returns>
        public DataTable GetMortarList()
        {
            return GetDataTableByQuery(QueryString.GET_MORTAR);
        }

        /// <summary>
        /// Get wastes list of work in Norm calcualte method
        /// </summary>
        /// <param name="congViec">The "CongTac" object</param>
        /// <returns>List wastes of work</returns>
        public BindingList<DinhMuc> GetWasteListInNormMethod(CongTac congViec, string province)
        {
            double dgvl = 0;
            double dgnc = 0;
            double dgcm = 0;
            BindingList<DinhMuc> result = new BindingList<DinhMuc>();
            DataTable dt = GetWasteByWorkCode(congViec.MaHieu, province);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    DinhMuc dm = new DinhMuc();
                    dm = DataUtilities.GetObjectFromDataRow(dr, typeof(DinhMuc)) as DinhMuc;
                    if (dm.GiaThuc <= 0)
                    {
                        dm.GiaThuc = dm.GiaGoc;
                    }
                    dm.ThanhTien = dm.KhoiLuong * dm.GiaThuc;
                    dm.KhoiLuongCan = congViec.KhoiLuong * dm.KhoiLuong;
                    dm.LoaiHaoPhi = DataUtilities.GetWasteTypeByWasteCode(dm.MaVatLieu);
                    result.Add(dm);
                    if (dm.LoaiHaoPhi.Equals(WasteType.E.ToString(),
                        StringComparison.OrdinalIgnoreCase))
                    {
                        dgnc += dm.ThanhTien;
                    }
                    else if (dm.LoaiHaoPhi.Equals(WasteType.F.ToString(),
                        StringComparison.OrdinalIgnoreCase))
                    {
                        dgcm += dm.ThanhTien;
                    }
                    else
                    {
                        dgvl += dm.ThanhTien;
                    }                    
                }
                // Cap nhat lai gia vat lieu khac
                DinhMuc vlKhac = result.Where(v => v.MaVatLieu.Equals(Constant.VL_KHAC, 
                                                StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                if (vlKhac != null)
                {
                    dgvl -= vlKhac.ThanhTien;
                    vlKhac.ThanhTien = (dgvl * vlKhac.KhoiLuong) / 100;
                    dgvl += vlKhac.ThanhTien;
                }
                // Cap nhat lai gia ca may khac
                DinhMuc cmKhac = result.Where(v => v.MaVatLieu.Equals(Constant.CM_KHAC,
                                                StringComparison.OrdinalIgnoreCase)).FirstOrDefault();
                if (cmKhac != null)
                {
                    dgcm -= cmKhac.ThanhTien;
                    cmKhac.ThanhTien = (dgcm * cmKhac.KhoiLuong) / 100;
                    dgcm += cmKhac.ThanhTien;
                }
                // replace "vua" if waste list have "hp vua"
                if (!string.IsNullOrEmpty(congViec.MacVuaChon))
                {
                    var query = result.Where(vua => vua.TenVatLieu.Trim()
                                            .Substring(0, 3).Equals(Constant.VUA, StringComparison.OrdinalIgnoreCase));
                    if (query.Count() > 0)
                    {
                        DinhMuc hpVua = query.First();
                        result.Remove(hpVua);
                        dgvl -= hpVua.ThanhTien;
                        List<DinhMuc> macVuaList = GetWasteListByMortar(congViec.MacVuaChon, hpVua.KhoiLuong, province);
                        if (macVuaList.Count > 0)
                        {
                            foreach (DinhMuc dm in macVuaList)
                            {
                                dgvl += dm.ThanhTien;
                                result.Add(dm);
                            }
                        }
                    }
                }

                congViec.DGVL = dgvl * congViec.HSVL;
                congViec.DGNC = dgnc * congViec.HSNC;
                congViec.DGCM = dgcm * congViec.HSCM;
                congViec.TTVL = congViec.KhoiLuong * congViec.DGVL.Value;
                congViec.TTNC = congViec.KhoiLuong * congViec.DGNC.Value;
                congViec.TTCM = congViec.KhoiLuong * congViec.DGCM.Value;
                congViec.TongCong = congViec.TTVL + congViec.TTNC + congViec.TTCM;
            }
            return result;
        }

        /// <summary>
        /// Get wastes list of work in Province calculate method
        /// </summary>
        /// <param name="congViec">The "CongTac" object</param>
        /// <returns>List wastes of work</returns>
        public BindingList<DinhMuc> GetWasteListInProvinceMethod(CongTac congViec, string province)
        {
            BindingList<DinhMuc> result = new BindingList<DinhMuc>();
            DataTable dt = GetWasteByWorkCode(congViec.MaHieu, province);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    DinhMuc dm = new DinhMuc();
                    dm = DataUtilities.GetObjectFromDataRow(dr, typeof(DinhMuc)) as DinhMuc;
                    if (dm.GiaThuc <= 0)
                    {
                        dm.GiaThuc = dm.GiaGoc;
                    }
                    dm.ThanhTien = dm.KhoiLuong * dm.GiaThuc;
                    dm.KhoiLuongCan = congViec.KhoiLuong * dm.KhoiLuong;
                    dm.LoaiHaoPhi = DataUtilities.GetWasteTypeByWasteCode(dm.MaVatLieu);
                    result.Add(dm);
                }
                // replace "vua" if waste list have "hp vua"
                if (!string.IsNullOrEmpty(congViec.MacVuaChon))
                {
                    DinhMuc hpVua = result.Where(vua => vua.TenVatLieu.Trim()
                                            .Substring(0, 3).ToLower() == Constant.VUA)
                                            .FirstOrDefault();
                    if (hpVua != null)
                    {
                        result.Remove(hpVua);
                        List<DinhMuc> macVuaList = GetWasteListByMortar(congViec.MacVuaChon, hpVua.KhoiLuong, province);
                        if (macVuaList.Count > 0)
                        {
                            foreach (DinhMuc dm in macVuaList)
                            {
                                result.Add(dm);
                            }
                        }
                    }
                }
            }
            congViec.TTVL = congViec.KhoiLuong * congViec.DGVL.Value * congViec.HSVL;
            congViec.TTNC = congViec.KhoiLuong * congViec.DGNC.Value * congViec.HSNC;
            congViec.TTCM = congViec.KhoiLuong * congViec.DGCM.Value * congViec.HSCM;
            return result;
        }

        /// <summary>
        /// Get waste list by mortar
        /// </summary>
        /// <param name="macVua">Mac vua code</param>
        /// <param name="dmVua">Dinh muc vua</param>
        /// <param name="province"></param>
        /// <returns></returns>
        public List<DinhMuc> GetWasteListByMortar(string macVua, double dmVua, string province)
        {
            List<DinhMuc> result = new List<DinhMuc>();
            DataTable dt = GetWasteByWorkCode(macVua, province);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    DinhMuc dm = new DinhMuc();
                    dm = DataUtilities.GetObjectFromDataRow(dr, typeof(DinhMuc)) as DinhMuc;
                    if (dm.GiaThuc <= 0)
                    {
                        dm.GiaThuc = dm.GiaGoc;
                    }
                    dm.KhoiLuong = dmVua * dm.KhoiLuong;
                    dm.ThanhTien = dm.KhoiLuong * dm.GiaThuc;
                    dm.LoaiHaoPhi = DataUtilities.GetWasteTypeByWasteCode(dm.MaVatLieu);
                    dm.KhoiLuongCan = dm.KhoiLuongCan * dmVua;
                    dm.IsVua = true;
                    result.Add(dm);
                }
            }
            return result;
        }

        /// <summary>
        /// Get waste by waste code
        /// </summary>
        /// <param name="wasteCode">The waste code</param>
        /// <param name="province">The province</param>
        /// <returns>DinhMuc</returns>
        public DinhMuc GetWasteByCode(string province, string wasteCode)
        {
            DataTable dt = GetDataTableByQuery(string.Format(QueryString.GET_WASTE_BY_WASTECODE, province, wasteCode));
            if (dt.Rows.Count > 0)
            {
                DinhMuc hp = new DinhMuc();
                hp = DataUtilities.GetObjectFromDataRow(dt.Rows[0], typeof(DinhMuc)) as DinhMuc;
                if (hp.GiaThuc <= 0)
                {
                    hp.GiaThuc = hp.GiaGoc;
                }
                hp.LoaiHaoPhi = DataUtilities.GetWasteTypeByWasteCode(hp.MaVatLieu);
                return hp;
            }
            return null;
        }

        #endregion
    }
}
